﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ThanhTu.GNC.Media.KQXS.DataAccess.DO;
using Oracle.ManagedDataAccess.Client;
using System.Data;

namespace ThanhTu.GNC.Media.KQXS.DataAccess.DAL
{
    public class MessengerSyntaxDAL : BaseDAL
    {
        public MessengerSyntaxDAL() : base() { }
        public int SaveMess(MessengerSyntaxDO vMessengerSyntaxDO)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "MESSENGERSYNTAX_PKG.SAVEMESSSYNTAX";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = vMessengerSyntaxDO.ProvinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_SYNTAX", OracleDbType.Varchar2);
                oraParam.Value = vMessengerSyntaxDO.Syntax;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_SWITCH_BOARD", OracleDbType.Varchar2);
                oraParam.Value = vMessengerSyntaxDO.SwitchBoard;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
                { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public int UpdateMess(MessengerSyntaxDO vMessengerSyntaxDO)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "MESSENGERSYNTAX_PKG.UpdateMessSyntax";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = vMessengerSyntaxDO.ProvinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_SYNTAX", OracleDbType.Varchar2);
                oraParam.Value = vMessengerSyntaxDO.Syntax;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_SWITCH_BOARD", OracleDbType.Varchar2);
                oraParam.Value = vMessengerSyntaxDO.SwitchBoard;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
                { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public int delete(string ProvinceID, string SWITCH_BOARD)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "MESSENGERSYNTAX_PKG.DELETEMESSSYNTAX";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = ProvinceID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_SWITCH_BOARD", OracleDbType.Varchar2);
                oraParam.Value = SWITCH_BOARD;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
                { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public MessengerSyntaxDO GetByMessSyntax(string Province_ID, string SWITCH_BOARD)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "MESSENGERSYNTAX_PKG.GETBYMESSSYNTAX";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_PROVINCE_ID", OracleDbType.Varchar2);
                oraParam.Value = Province_ID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_Switch_Board", OracleDbType.Varchar2);
                oraParam.Value = SWITCH_BOARD;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataRow dr = null;
                    MessengerSyntaxDO vMessengerSyntaxDO = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                dr = ds.Tables[0].Rows[0];
                                vMessengerSyntaxDO = new MessengerSyntaxDO();

                                if (!Convert.IsDBNull(dr["PROVINCEID"]))
                                { vMessengerSyntaxDO.ProvinceID = Convert.ToString(dr["PROVINCEID"]); }

                                if (!Convert.IsDBNull(dr["SYNTAX"]))
                                { vMessengerSyntaxDO.Syntax = Convert.ToString(dr["SYNTAX"]); }

                                if (!Convert.IsDBNull(dr["SWITCHBOARD"]))
                                { vMessengerSyntaxDO.SwitchBoard = Convert.ToString(dr["SWITCHBOARD"]); }
                                dr = null;
                            }
                        }

                        ds.Dispose();
                    }
                    oraComm.Dispose();
                    oraParam = null;
                    return vMessengerSyntaxDO;
                }
            }
        }

        public MessengerSyntaxDO GetByMessSyntax_Syntax(string Syntax, string SWITCH_BOARD)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "MESSENGERSYNTAX_PKG.GetByMessSyntax_Syntax";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_Syntax", OracleDbType.Varchar2);
                oraParam.Value = Syntax;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_Switch_Board", OracleDbType.Varchar2);
                oraParam.Value = SWITCH_BOARD;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataRow dr = null;
                    MessengerSyntaxDO vMessengerSyntaxDO = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                dr = ds.Tables[0].Rows[0];
                                vMessengerSyntaxDO = new MessengerSyntaxDO();

                                if (!Convert.IsDBNull(dr["PROVINCEID"]))
                                { vMessengerSyntaxDO.ProvinceID = Convert.ToString(dr["PROVINCEID"]); }

                                if (!Convert.IsDBNull(dr["SYNTAX"]))
                                { vMessengerSyntaxDO.Syntax = Convert.ToString(dr["SYNTAX"]); }

                                if (!Convert.IsDBNull(dr["SWITCHBOARD"]))
                                { vMessengerSyntaxDO.SwitchBoard = Convert.ToString(dr["SWITCHBOARD"]); }
                                dr = null;
                            }
                        }

                        ds.Dispose();
                    }
                    oraComm.Dispose();
                    oraParam = null;
                    return vMessengerSyntaxDO;
                }
            }
        }
        public DataTable SelectAll_Messenger(int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "MESSENGERSYNTAX_PKG.GETALLMESSSYNTAX";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable SelectAll_Messenger_Region(string Region_id, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "MESSENGERSYNTAX_PKG.GETALLMESSSYNTAX_REGIONID";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_REGION_ID", OracleDbType.Varchar2);
                oraParam.Value = Region_id;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
    }
}
